﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace vnCommon
{
    public class commonDatabase
    { 
      //-----------------------------------------------------------------------//
        #region return DataTable SQL for SQLserver
        //Query returns DataTable
        public static DataTable GetSQLDataTable(string _ConnectionString, string _SQL, List<SqlParameter> _parameters)
        {
            SqlConnection conn = new SqlConnection(_ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand myCommand = conn.CreateCommand();
            myCommand.CommandText = _SQL;
            foreach (SqlParameter p in _parameters)
                myCommand.Parameters.Add(p);
            da.SelectCommand = myCommand;
            DataTable dt = new DataTable();
            conn.Open();
            da.Fill(dt);
            conn.Close();
            return dt;
        }

        public static DataTable GetSQLDataTable(string _ConnectionString,string _SQL)
        {
            SqlConnection conn = new SqlConnection(_ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand myCommand = conn.CreateCommand();
            myCommand.CommandText = _SQL;
            da.SelectCommand = myCommand;
            DataTable dt = new DataTable();
            conn.Open();
            da.Fill(dt);
            conn.Close();
            return dt;
        }

        #endregion
        //-----------------------------------------------------------------------//
        #region return DataSet SQL for SQLserver
        //Query returns dataset
        public static DataSet GetSQLDataSet(string _ConnectionString, string _SQL, List<SqlParameter> _parameters)
        {
            SqlConnection conn = new SqlConnection(_ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand myCommand = conn.CreateCommand();
            myCommand.CommandText = _SQL;
            foreach (SqlParameter p in _parameters)
                myCommand.Parameters.Add(p);
            da.SelectCommand = myCommand;
            DataSet ds = new DataSet();
            conn.Open();
            da.Fill(ds);
            conn.Close();
            return ds;
        }

        public static DataSet GetSQLDataSet(string _ConnectionString, string _SQL)
        {
            SqlConnection conn = new SqlConnection(_ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand myCommand = conn.CreateCommand();
            myCommand.CommandText = _SQL;
            da.SelectCommand = myCommand;
            DataSet ds = new DataSet();
            conn.Open();
            da.Fill(ds);
            conn.Close();
            return ds;
        }

        #endregion
      //-----------------------------------------------------------------------//
        #region ExecuteNonQuery SQLserver
        //Non Query
        public static int ExecuteNonQuerySQL(string _ConnectionString, string _SQL)
        {
            try
            {
                SqlConnection conn = new SqlConnection(_ConnectionString);
                SqlCommand myCommand = new SqlCommand(_SQL, conn);
                myCommand.Connection.Open();
                int result = myCommand.ExecuteNonQuery();
                conn.Close();
                return result;
            }
            catch
            {
                return 0;
            }
        }

        public static int ExecuteNonQuerySQL(string _ConnectionString, string _SQL, List<SqlParameter> _parameters)
        {
            SqlConnection conn = new SqlConnection(_ConnectionString);
            SqlCommand myCommand = new SqlCommand(_SQL, conn);

            foreach (SqlParameter p in _parameters)
                myCommand.Parameters.Add(p);

            myCommand.Connection.Open();
            int result = myCommand.ExecuteNonQuery();

            conn.Close();
            return 0;
        }
        #endregion
      //-----------------------------------------------------------------------//
        #region ExecuteScalar SQLserver
        //Return a single value
        public static object ExecuteScalarSQL(string _SQL, string _ConnectionString)
        {
            SqlConnection conn = new SqlConnection(_ConnectionString);
            SqlCommand myCommand = new SqlCommand(_SQL, conn);
            myCommand.Connection.Open();
            object result = myCommand.ExecuteScalar();
            conn.Close();
            return result;
        }

        public static object ExecuteScalarSQL(string _SQL, string _ConnectionString, List<SqlParameter> _parameters)
        {
            SqlConnection conn = new SqlConnection(_ConnectionString);
            SqlCommand myCommand = new SqlCommand(_SQL, conn);

            foreach (SqlParameter p in _parameters)
                myCommand.Parameters.Add(p); 
            myCommand.Connection.Open();
            object result = myCommand.ExecuteScalar();
            conn.Close();
            return result;
        }
        #endregion
    }
}
